/*
 * Copyright (c) 2010-2020 IISI.
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of IISI.
 */
package com.iisigroup.log.analysis.rdbms;
 
import com.iisigroup.log.analysis.model.StuckThreadRecord;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 */
public class TableJDBCDao {
    //================================================
    //== [Enumeration types] Block Start
    //== [Enumeration types] Block End 
    //================================================
    //== [static variables] Block Start
    private final static Logger LOGGER = LoggerFactory.getLogger(TableJDBCDao.class);
    //== [static variables] Block Stop 
    //================================================
    //== [instance variables] Block Start
    //== [instance variables] Block Stop 
    //================================================
    //== [static Constructor] Block Start
    //== [static Constructor] Block Stop 
    //================================================
    //== [Constructors] Block Start (含init method)
    //== [Constructors] Block Stop 
    //================================================
    //== [Static Method] Block Start
    //== [Static Method] Block Stop 
    //================================================
    //== [Accessor] Block Start
    //== [Accessor] Block Stop 
    //================================================
    //== [Overrided Method] Block Start (Ex. toString/equals+hashCode)
    //== [Overrided Method] Block Stop 
    //================================================
    //== [Method] Block Start
    //####################################################################
    public static Connection getInstance() {       
//        String url = "jdbc:h2:mem:test;INIT=create schema if not exists test\\;runscript from '~/sql/populate.sql';DB_CLOSE_DELAY=-1";
//        String url = "jdbc:h2:mem:test;INIT=create schema if not exists test\\;runscript from './sql/populate.sql';DB_CLOSE_DELAY=-1";
        String url = "jdbc:h2:./dbdata/testdbh2;INIT=create schema if not exists test\\;runscript from './sql/populate.sql';";
        String username = "sa";
        String password = "";
//        String driver = "org.hsqldb.jdbcDriver";
        String driver = "org.h2.Driver";
        //org.h2.Driver
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            LOGGER.error(e.getMessage() , e) ;
        } catch (ClassNotFoundException e) {
            LOGGER.error(e.getMessage() , e) ;
        }
        return conn;
    }
    public void insertData(final List<StuckThreadRecord> list){
        final Connection conn = getInstance();
        final  String sqlTemplate = "INSERT INTO STUCK (UID, HOST_IP , DATE , TIME , CLIENT_IP , USER_ID , STACKTRACE ) VALUES (%s ,'%s' ,'%s','%s','%s','%s','%s' ) ;";
        final  String sqlTemplateNull = "INSERT INTO STUCK (UID, HOST_IP , DATE , TIME , CLIENT_IP , USER_ID , STACKTRACE ) VALUES (%s ,'%s' ,'%s','%s', null , null,'%s' ) ;";
        Statement stmt;
        try {
            stmt = conn.createStatement();
            int count =0;
            for(StuckThreadRecord str : list ){
                count++;
                String sql = String.format(sqlTemplate,count , str.getHostIp() , str.getDate() ,str.getTime() , str.getClientIp() ,str.getUserId() ,str.getStackTrace());
                String sql1 = String.format(sqlTemplateNull,count , str.getHostIp() , str.getDate() ,str.getTime() ,   str.getStackTrace());
                if(str.getClientIp() == null){
                    stmt.addBatch(sql1);
                }else{
                    stmt.addBatch(sql);
                }
               
            }
            stmt.executeBatch();
        } catch (SQLException e) {
             LOGGER.error(e.getMessage() , e) ;
        } finally {
            closeConnection(conn);
        }
    } 
    public void insertStuckEventData(final int initUid ,  final Map<String,Integer> map){
        final Connection conn = getInstance();
        
        final String querySQL ="select * from stuck";
        final String sqlTemplate = "INSERT INTO STUCKEVENT (UID, HOST_IP , DATE , TIME , STACKTRACE_UID,CLIENT_IP,USER_ID ) VALUES (%s ,'%s' ,'%s','%s','%s','%s','%s' ) ;";
        final String sqlTemplateNull = "INSERT INTO STUCKEVENT (UID, HOST_IP , DATE , TIME , STACKTRACE_UID,CLIENT_IP,USER_ID) VALUES (%s ,'%s' ,'%s','%s','%s', null , null ) ;";
        Statement stmt;
        Statement stmt2;
        try {
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            stmt2= conn.createStatement();
            final ResultSet rs = stmt.executeQuery(querySQL);
            Integer uid = initUid+1;
            while (rs.next()) {
                // Retrieve by column name
                final String stacktrace = rs.getString("stacktrace"); 
                final Integer stacktraceUid = map.get(stacktrace);
                if(stacktraceUid == null ){
                    continue;
                }
                final String hostIp = rs.getString("HOST_IP"); 
                final String date = rs.getString("DATE"); 
                final String time = rs.getString("TIME"); 
                final String clientIp = rs.getString("CLIENT_IP");
                final String userId = rs.getString("USER_ID");
                if(clientIp==null || StringUtils.equalsIgnoreCase("null", clientIp)){                  
                   String sql =   String.format(sqlTemplateNull,  (uid++).toString(),hostIp,date, time ,stacktraceUid);
                   stmt2.executeUpdate(sql);
                   
                }else{
                    String sql =  String.format(sqlTemplate,(uid++).toString(),hostIp,date, time ,stacktraceUid ,clientIp ,userId);
                    stmt2.executeUpdate(sql);
                    
                }
            }
            conn.commit();
            rs.close();
            stmt.close();
            stmt2.close();
        } catch (SQLException e) {
             LOGGER.error(e.getMessage() , e) ;
             if(conn!=null){
                 try {
                    conn.rollback();
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                   LOGGER.error("", e1);
                }
             }
        } finally {
            closeConnection(conn);
        }
    } 
    
    public List<String> getDistinctStucktraceList(boolean onlineOperation) {
        final List<String> result = new ArrayList<String>();
        final Connection conn = getInstance();
        Statement stmt;
        try {
            stmt = conn.createStatement();
            final String sqloffline = "select distinct stacktrace  from stuck where client_ip is null ;  ";
            final String sqlonline = "select distinct stacktrace  from stuck where client_ip is not null ;  ";
            final ResultSet rs = stmt.executeQuery(onlineOperation ?sqlonline:sqloffline);
            while (rs.next()) {
                // Retrieve by column name
                final String stacktrace = rs.getString("stacktrace"); 
                result.add(stacktrace);
            }
            rs.close();
        } catch (SQLException e) {
             LOGGER.error(e.getMessage() , e) ;
        } finally {
            closeConnection(conn);
        }
        return result;
    }
    public int countTableSize(String tableName){
        int result =0;
        final Connection conn = getInstance();
        Statement stmt;
        try {
            stmt = conn.createStatement();
            String sql = String.format("SELECT  COUNT(*) AS rowcount  FROM %s ",tableName);
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                // Retrieve by column name
                 
                result = rs.getInt("rowcount") ;
 
            }
            rs.close();
        } catch (SQLException e) {
             LOGGER.error(e.getMessage() , e) ;
        } finally {
            closeConnection(conn);
        }
        return result;
    } 
    public List<String[]> getPersonIdSiteIdList() {
        final List<String[]> result = new ArrayList<String[]>();
        final Connection conn = getInstance();
        Statement stmt;
        try {
            stmt = conn.createStatement();
            String sql = "SELECT   person_id,site_id FROM rldf004m where personal_mark='0'  ";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                // Retrieve by column name

                final String personId = rs.getString("person_id");
                final String siteId = rs.getString("site_id");

                // Display values
                //System.out.print("person_id: " + personId);
                //System.out.println("site_id: " + siteId);
                result.add(new String[] { personId, siteId });
            }
            rs.close();
        } catch (SQLException e) {
             LOGGER.error(e.getMessage() , e) ;
        } finally {
            closeConnection(conn);
        }
        return result;
    }
    private void closeConnection(final Connection conn ){
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                 LOGGER.error(e.getMessage() , e) ;
            }
        }
    }
    //## [Method] sub-block : 
    //####################################################################    
    //== [Method] Block Stop 
    //================================================
    //== [Inner Class] Block Start
    //== [Inner Class] Block Stop 
    //================================================
}
